<?php
/**
 * Description
 * User: duanwenjie
 * Date: 2019/5/9
 * Time: 11:59 AM
 */

namespace app\index\service;
use app\common\ConfigBase;
use app\common\model\ToolModel;
use app\common\service\DownService;
use app\common\service\SkuService;
use think\Db;
use think\Exception;

class DrawplanService
{
    static public function getDrawplanList($param, $is_download = false)
    {
        try{
            $where = "where 1 = 1 ";
            $pageNumber = isset($param['page']) ? $param['page'] : 1;
            $pageData = isset($param['limit']) ? $param['limit'] : 20;
            $draw_plan_sn = isset($param['draw_plan_sn']) ? $param['draw_plan_sn'] : '';  //提货计划单号
            $po = isset($param['po']) ? $param['po'] : '';  //po单号
            $first_arrive_time = isset($param['first_arrive_time']) ? $param['first_arrive_time'] : '';  //最早到货日期
            $last_arrive_time = isset($param['last_arrive_time']) ? $param['last_arrive_time'] : '';  //最晚到货日期时间
            $status = isset($param['status']) ? $param['status'] : '';  //状态
            $offset = ($pageNumber - 1) * $pageData;
            $limit = " LIMIT $offset,$pageData";

            if (!empty($draw_plan_sn)) {
                $draw_plan_sn = trim($draw_plan_sn);
                $where .= " AND draw_plan_sn = '{$draw_plan_sn}'";
            }
            if (!empty($po)) {
                $po = trim($po);
                $where .= " AND po = '{$po}'";
            }
            if (!empty($first_arrive_time)) {
                $where .= " AND first_arrive_time >= '{$first_arrive_time}'";
            }
            if (!empty($last_arrive_time)) {
                $where .= " AND last_arrive_time <= '{$last_arrive_time}'";
            }
            if (!empty($status)) {
                $where .= " AND status = {$status}";
            }

            $sqlCount = "SELECT COUNT(id) num FROM draw_plan {$where}";
            $countArr = ToolModel::queryData($sqlCount);
            if (empty($countArr) || $countArr[0]['num'] == 0) {
                if ($is_download) exitJson(["code" => "500", "msg" => "无有效数据"]);
                return array('total' => 0, 'list' => array());
            }
            $fileds = 'id,draw_plan_sn,po,po_quantity,sku,sku_name,remaining_quantity,present_quantity,
                      first_arrive_time,last_arrive_time,upload,operator,update_time,status,
                      case status when 1 then "已确定" when -1 then "未确定" end status_name';

            $sql = "SELECT {$fileds} FROM draw_plan {$where} ORDER BY id DESC {$limit}";
            if ($is_download) return "SELECT {$fileds} FROM draw_plan {$where} ORDER BY id DESC ";

            $list = ToolModel::queryData($sql);
            $data = array();
            $data['total'] = intval($countArr[0]['num']);
            $data['list'] = $list;

            return $data;
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }


    /**
     * 校验新增/编辑数据
     * @param $params
     * @param string $id
     * @return array
     * @throws Exception
     * @author duanwenjie
     */
    static public function checkData($params, $id = "")
    {
        try{
            $shop_name = $params['shop_name'];
            $drawplan_name = $params['drawplan_name'];
            /*校验店铺*/
            $res = Db::name('base_shop')->where('shop_name', '=', $shop_name)->find();
            if (empty($res)) returnJson(200, "操作失败！店铺：({$shop_name})不存在");
            $base_shop_id = $res['id']; // 店铺ID
            $base_company_id = $res['base_company_id']; // 公司ID
            $base_country_id = $res['base_country_id']; // 国家ID
            $base_country_name = $res['base_country_name']; // 国家名称
            if (empty($id)){
                /*新增需校验仓库*/
                $res3 = Db::name('draw_plan')->where('drawplan_name', '=', $drawplan_name)->find();
                if (!empty($res3)) returnJson(200, "操作失败！仓库：({$drawplan_name})已存在");
            }else{
                $res_ = Db::name('draw_plan')->where('id', '=', $id)->find();

                if (isset($res_['drawplan_name']) && $res_['drawplan_name'] != $params['drawplan_name']){
                    /*编辑的仓库名称不是自己需校验仓库*/
                    $res4 = Db::name('draw_plan')->where('drawplan_name', '=', $drawplan_name)->find();
                    if (!empty($res4)) returnJson(200, "操作失败！仓库：({$drawplan_name})已存在");
                }
            }
            $data = array("base_company_id" => $base_company_id, "base_country_id" => $base_country_id, "base_country_name" => $base_country_name, "base_shop_id" => $base_shop_id);

            return $data;
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }


    /**
     * 上传数据处理
     * @param $data
     * @param $param
     * @return array
     * @throws Exception
     * @author duanwenjie
     */
    static public function uploadHandle($data, $param)
    {
        try{
            $line = 1;
            $error = array();
            $pos = array();
            $shops = array();
            $skus = array();
            $data_ = array();
            $exist_po_sku_can_quantity = self::sureDrawplan(array("is_need_info" => true));
            foreach ($data as $itm){
                $line ++;
                if (empty(trim($itm[0])) && empty(trim($itm[1])) && empty(trim($itm[2]))
                    && empty(trim($itm[3])) && empty(trim($itm[4])) && empty(trim($itm[5]))) break;
                if (empty(trim($itm[0]))) {
                    $error[] = '第' . $line . '行PO单号为空';
                    continue;
                }
                if (empty(trim($itm[1]))) {
                    $error[] = '第' . $line . '行店铺为空';
                    continue;
                }
                if (empty(trim($itm[2]))) {
                    $error[] = '第' . $line . '行SKU为空';
                    continue;
                }
                if (empty(trim($itm[3]))) {
                    $error[] = '第' . $line . '行理论需求数量为空';
                    continue;
                }
                if (empty(trim($itm[4]))) {
                    $error[] = '第' . $line . '行本次提货数量为空';
                    continue;
                }
                if (empty(trim($itm[5]))) {
                    $error[] = '第' . $line . '行最早到货日期为空';
                    continue;
                }
                if (empty(trim($itm[6]))) {
                    $error[] = '第' . $line . '行最晚到货日期为空';
                    continue;
                }
                if(strtotime(trim($itm[5])) > strtotime(trim($itm[6]))){
                    $error[] = '第' . $line . '行最早到货日期大于最晚到货日期';
                    continue;
                }
                $pos_info = self::getPoSkuInfos(trim($itm[0]),trim($itm[2])); // PO单SKU相关信息
                if (isset($pos_info['code'])) {
                    $error[] = '第' . $line . "行采购单：({$itm[0]})下SKU：({$itm[2]})在系统中不存在";
                    continue;
                }
                $max_quantity = isset($pos_info['max_quantity']) ? $pos_info['max_quantity'] : 0;
                if (intval(trim($itm[4])) > $max_quantity){
                    $error[] = '第' . $line . "行本次提货数量：{$itm[4]} 大于采购单：({$itm[0]})下SKU：({$itm[2]})最大可录入数量：{$max_quantity}";
                    continue;
                }
                $k = trim($itm[0])."_".trim($itm[2]);
                if (isset($exist_po_sku_can_quantity[$k])){
                    $can_quantity = $exist_po_sku_can_quantity[$k];
                    if (intval(trim($itm[4])) > $can_quantity){
                        $error[] = '第' . $line . "行本次提货数量：{$itm[4]} 大于采购单：({$itm[0]})下SKU：({$itm[2]})最大可提货数量：{$can_quantity}";
                        continue;
                    }
                }
                $itm['po_quantity'] = isset($pos_info['quantity']) ? $pos_info['quantity'] : 0;
                $data_[] = $itm;
                $pos[] = trim($itm[0]);
                $shops[] = trim($itm[1]);
                $skus[] = trim($itm[2]);
            }

            if (!empty($data_)) {
                $error1 = PurchaseplanService::checkSkus($skus);
                $error2 = PurchaseplanService::checkShop($shops);
                $error3 = self::checkPo($pos);
                if (!empty($error1)) $error = array_merge($error,$error1);
                if (!empty($error2)) $error = array_merge($error,$error2);
                if (!empty($error3)) $error = array_merge($error,$error3);
            }else{
                $error[] = "无有效数据";
            }
            $exist_items = array();
            $exist_pos = array();
            if(!empty($error)){
                return ["code" => "500", "msg" => join('<br/>',$error)];
            }else{
                ToolModel::startTrans();
                if (!empty($data_)){
                    $shop_names_infos = PurchaseplanService::checkShop(array(),true); // 店铺数据
                    $skus = array_column(array_values($data_),'2'); // sku数组
                    $skus_info = PurchaseplanService::checkSkus($skus,true); // SKU相关信息
                    // 插入数据到数据库 draw_plan draw_plan_detail
                    $po_quantity_all = array();
                    $exist_draw_plan_ids = array();
                    foreach ($data_ as $value){
                        $tmb_str = json_encode($value);
                        $tmb_str2 = trim($value[0])."_".trim($value[2]); // 以PO_SKU作为唯一键 （有重复的只累加本次提货数量，只新增提货明细）
                        if (in_array($tmb_str,$exist_items)) continue; // 重复的数据跳过
                        /*已存在的采购单号_SKU 本次提货数量累加,只插入 draw_plan_detail 表数据*/
                        if (in_array($tmb_str2,$exist_pos)){
                            $po_quantity_all[$tmb_str2] += trim($value[4]);
                            if ($po_quantity_all[$tmb_str2] > $value['po_quantity']){
                                ToolModel::rollback();
                                return ["code" => "500", "msg" => "采购单:{$value[0]}下SKU:{$value[2]}录入的本次提货数量累计:({$po_quantity_all[$tmb_str2]})大于采购单数量:({$value['po_quantity']})"];
                            }
                            $draw_plan_id = isset($exist_draw_plan_ids[$tmb_str2]) ? $exist_draw_plan_ids[$tmb_str2] : 0;
                            $temp3 = array(
                                "draw_plan_id" => $draw_plan_id,
                                "base_country_name" => isset($shop_names_infos[trim($value[1])]['base_country_name']) ? $shop_names_infos[trim($value[1])]['base_country_name'] : '',
                                "base_shop_id" => isset($shop_names_infos[trim($value[1])]['id']) ? $shop_names_infos[trim($value[1])]['id'] : 0,
                                "base_shop_name" => trim($value[1]),
                                "need_quantity" => trim($value[3]),
                                "actual_quantity" => trim($value[4])
                            );
                            $draw_plan_detail_id1 = ToolModel::insertData($temp3,'draw_plan_detail');
                            $present_quantity = $po_quantity_all[$tmb_str2]; // 本次提货量(累加后)
                            ToolModel::updateData(array("id" => $draw_plan_id),array("present_quantity" => $present_quantity),'draw_plan');
                            continue;
                        }else{
                            $po_quantity_all[$tmb_str2] = trim($value[4]);
                            if ($po_quantity_all[$tmb_str2] > $value['po_quantity']){
                                ToolModel::rollback();
                                return ["code" => "500", "msg" => "采购单:{$value[0]}下SKU:{$value[2]}录入的本次提货数量累计:({$po_quantity_all[$tmb_str2]})大于采购单数量:({$value['po_quantity']})"];
                            }
                        }
                        $exist_items[] = $tmb_str;
                        $exist_pos[] = $tmb_str2;
                        // 先插入 draw_plan 表 再插入 draw_plan_detail 表
                        $draw_plan_sn = ToolModel::createSerialNumber('TH1','4'); // 生成提货计划单号

                        $temp = array(
                            "draw_plan_sn" => $draw_plan_sn,
                            "po" => trim($value[0]),
                            "po_quantity" => $value['po_quantity'],
                            "sku" => trim($value[2]),
                            "sku_name" => in_array(trim($value[2]),array_keys($skus_info)) ? $skus_info[trim($value[2])]['name'] : '',
                            "present_quantity" => isset($po_quantity_all[$tmb_str2]) ? $po_quantity_all[$tmb_str2] : 0,
                            "remaining_quantity" => $value['po_quantity'],
                            "first_arrive_time" => trim($value[5]),
                            "last_arrive_time" => trim($value[6]),
                            "upload" =>  CN_NAME ? CN_NAME : 'admin'
                        );
                        $draw_plan_id = ToolModel::insertData($temp,'draw_plan');
                        $exist_draw_plan_ids[$tmb_str2] = $draw_plan_id;
                        $temp2 = array(
                            "draw_plan_id" => $draw_plan_id,
                            "base_country_name" => isset($shop_names_infos[trim($value[1])]['base_country_name']) ? $shop_names_infos[trim($value[1])]['base_country_name'] : '',
                            "base_shop_id" => isset($shop_names_infos[trim($value[1])]['id']) ? $shop_names_infos[trim($value[1])]['id'] : 0,
                            "base_shop_name" => trim($value[1]),
                            "need_quantity" => trim($value[3]),
                            "actual_quantity" => trim($value[4])
                        );
                        $draw_plan_detail_id2 = ToolModel::insertData($temp2,'draw_plan_detail');
                    }
                }
                ToolModel::commit();
                self::sureDrawplan(array("is_need_update" => true)); // 插入完数据后，再整体更新一下本次结余的数量
                return ["code" => "1", "msg" => "成功"];
            }
        }catch (Exception $e){
            ToolModel::rollback();
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }

    /**
     * 校验PO单号是否存在 （hz_upload_batch_details 表中 real_purchaseorder_id 字段）
     * @param array $pos
     * @param bool $is_need_poinfos
     * @return array
     * @author duanwenjie
     */
    static public function checkPo($pos = array(), $is_need_poinfos = false)
    {
        $res = Db::name('hz_upload_batch_details')->alias('HZUBD')
            ->join('hz_purchaseorders HZP','HZUBD.real_purchaseorder_id = HZP.id','left')
            ->where('HZP.status','=',20) // 只有已打印的采购单才能使用
            ->field('HZUBD.real_purchaseorder_id')
            ->column('HZUBD.real_purchaseorder_id,HZP.status','HZUBD.real_purchaseorder_id');
        $error = array();
        if ($res == array(0)) $res = array();
        if ($is_need_poinfos) return $res;
        foreach ($pos as $v){
            if (!in_array($v,array_keys($res))) $error[] = "PO单号：({$v})在系统中不存在，或状态不是：已打印";
        }

        return $error;
    }


    /**
     * 通过PO、SKU获取PO数量，最大可录入数量
     * @param $po
     * @param $sku
     * @return array|false|\PDOStatement|string|\think\Model
     * @author duanwenjie
     */
    static public function getPoSkuInfos($po, $sku)
    {
        $res = Db::name('hz_purchaseorder_details')
            ->where('purchaseorder_id','=',$po)
            ->where('sku','=',$sku)
            ->field('quantity, IFNULL((quantity-ware_quantity),0) as max_quantity')
            ->find();

        if (empty($res)) $res['code'] = -1;

        return $res;
    }

    /**
     * 导出数据
     * @param $param
     * @return array
     * @author duanwenjie
     */
    static public function export($param)
    {
        $sql = self::getDrawplanList($param,true);
        $title = array(
            'draw_plan_sn' => '提货计划单号',
            'po' => 'PO单号',
            'sku' => 'SKU',
            'sku_name' => 'SKU中文名称',
            'po_quantity' => 'PO数量',
            'remaining_quantity' => '提货结余数量',
            'present_quantity' => '本次提货数量',
            'first_arrive_time' => '最早到货日期',
            'last_arrive_time' => '最晚到货日期',
            'upload' => '上传人',
            'operator' => '修改人',
            'update_time' => '修改时间',
            'status_name' => '状态'
        );

        $res = DownService::getData($sql,$title,'',$param);

        return $res;
    }


    /**
     * 批量确定提货计划
     * @param $param
     * @return array
     * @throws Exception
     * @author duanwenjie
     */
    static public function sureDrawplan($param)
    {
        try{
            /*算出批量确定的PO单所有已确定的提货数量 PO+SKU维度*/
            $res1 = Db::name('draw_plan')->field('id,po,po_quantity,sku,present_quantity,status')->select();
            $exist_po_sku_sure = array();
            $exist_po_sku_can_quantity = array();
            if (!empty($res1)){
                foreach ($res1 as $value){
                    $k = $value['po']."_".$value['sku'];
                    if (in_array($k,array_keys($exist_po_sku_sure))){
                        $exist_po_sku_sure[$k] += $value['present_quantity'];
                    }else{
                        $exist_po_sku_sure[$k] = $value['present_quantity'];
                    }
                }
                foreach ($res1 as $value_){
                    $k_ = $value_['po']."_".$value_['sku'];
                    $quantity_ = isset($exist_po_sku_sure[$k_]) ? $exist_po_sku_sure[$k_] : 0;
                    $exist_po_sku_can_quantity[$k_] = $value_['po_quantity'] - $quantity_;
                }
            }
            if (isset($param['is_need_info'])) return $exist_po_sku_can_quantity;
            if (isset($param['is_need_update'])){
                foreach ($res1 as &$val){
                    $key = $val['po']."_".$val['sku'];
                    if (isset($exist_po_sku_can_quantity[$key])){
                        $val['remaining_quantity'] = $exist_po_sku_can_quantity[$key];
                    }
                }
                return ToolModel::dbInsertOrUpdate('draw_plan',$res1);
            };
            $ids = $param['id'];
            $data = Db::name('draw_plan')->field('*')->where('id', 'in', $ids)->where('status', '=', '-1')->select();
            $error = array();
            $update_data = array();
            if (!empty($data)){
                foreach ($data as $v){
                    $k__ = $v['po']."_".$v['sku']; // PO单+SKU维度
                    $po_can_quantity = isset($exist_po_sku_can_quantity[$k__]) ? $exist_po_sku_can_quantity[$k__] : 0; // 采购单允许的最大提货数量 = 采购单数量 - 所有已确定提货的数量之和
                    if ($v['present_quantity'] > $v['po_quantity']){
                        $error[] = "提货单：({$v['draw_plan_sn']})的本次提货量({$v['present_quantity']})不能大于采购单的数量({$v['po_quantity']})";
                        continue;
                    }
                    if ($v['present_quantity'] > $po_can_quantity){
                        $error[] = "提货单：({$v['draw_plan_sn']})的本次提货量({$v['present_quantity']})不能大于采购单允许的提货数量({$po_can_quantity})";
                        continue;
                    }
                    $update_data[] = array(
                        "id" => $v['id'],
                        "remaining_quantity" => $v['po_quantity'] - $v['present_quantity'],
                        "operator" =>  CN_NAME ? CN_NAME : '系统',
                        "status" => 1
                        );
                }
            }
            if (!empty($error)) returnJson('500', join('<br/>', $error));
            $res = ToolModel::dbInsertOrUpdate('draw_plan', $update_data); // 批量更新数据
            returnJson('1', '操作成功');

        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }
    }

    /**
     * 获取提货明细
     * @param $param
     * @return false|\PDOStatement|string|\think\Collection
     * @author duanwenjie
     */
    static public function getDrawPlanDetail($param)
    {
        $id = $param['id'];
        $res = Db::name('draw_plan_detail')->field('*')->where('draw_plan_id','=',$id)->select();

        return $res;
    }


    /**
     * 编辑提货计划详情
     * @param $param
     * @throws Exception
     * @author duanwenjie
     */
    static public function edit($param)
    {
        try{
            $update_data1 = array();
            $update_data2 = array();
            $update_deatil_data = array();
            $actual_quantity_all = 0;
            if (!empty($param['need_quantity']) && !empty($param['actual_quantity']) && !empty($param['detai_id'])){
                foreach ($param['need_quantity'] as $key => $value){
                    $update_deatil_data[] = array(
                        "id" => $param['detai_id'][$key],
                        "need_quantity" => $value,
                        "actual_quantity" => $param['actual_quantity'][$key]
                    );
                    $actual_quantity_all += $param['actual_quantity'][$key];
                }

                $update_data1 = array(
                    "id" => $param['id'],
                    "present_quantity" => $actual_quantity_all,
                    "operator" => $param['operator']
                );
            }

            /*算出批量确定的PO单所有已确定的提货数量*/
            $data_sure_all = Db::name('draw_plan')->field('*')->where('status', '=', '1')->select();
            $exist_sure_pos = array_column(array_values($data_sure_all),'po');

            $checkPo = array();
            if (!empty($exist_sure_pos)){
                $checkPo = Db::name('draw_plan')->field('SUM(present_quantity) sure_all_quantity,po')->where('po', 'in', $exist_sure_pos)->where('status', '=', '1')->group('po')->column('SUM(present_quantity) sure_all_quantity,po','po');
            }

            $res = self::getPoSkuInfos($param['po_'],$param['sku_']);
            if (!empty($res)){
                $po_can_quantity = $res['quantity'] - (isset($checkPo[$param['po_']]) ? $checkPo[$param['po_']] : 0); // 采购单允许的最大提货数量 = 采购单数量 - 所有已确定提货的数量之和
                if ($actual_quantity_all > $po_can_quantity) returnJson('500',"累计实际提货数量({$actual_quantity_all})不能大于采购单允许的提货数量({$po_can_quantity})");
            }


            if (!empty($param['th_time'])){
                $date = explode(' - ', $param['th_time']);
                if (count($date) == 2){
                    $update_data2 = array(
                        "first_arrive_time" => $date[0],
                        "last_arrive_time" => $date[1]
                    );
                }
            }

            $update_data = array_merge($update_data1,$update_data2);
            if (!empty($update_data)) $res = ToolModel::dbInsertOrUpdate('draw_plan',[$update_data]);
            if (!empty($update_deatil_data)) $res2 = ToolModel::dbInsertOrUpdate('draw_plan_detail',$update_deatil_data);
            self::sureDrawplan(array("is_need_update" => true));
        }catch (Exception $e){
            trace($e->getMessage(), 'error');
            throw new Exception($e->getMessage());
        }

    }

}